RE: [GENERAL] changing between 6.4.1 and 6.5

At 8:38 am +0200 26/8/99, Jens Felber wrote:
>At 18:02 25.08.99 +0200, dpeder wrote:
>>have You set any triggers, rules, listens or defaults for tablex?
>>
>
>
>Nothing is set. I create a new table for tests.
>
> --> create table test1 (x1 int2, x2 int2, tx char);
>
>then I've inserted some values:
>
>--> insert into test1 values (1,2, 'a');
>--> insert into test1 values (1,2, 'b');
>--> insert into test1 values (1,3, 'b');
>--> insert into test1 values (1,4, 'b');
>--> insert into test1 values (2,2, 'b');
>--> insert into test1 values (2,3, 'b');
>
>after that I want a select with group by:
>
>--> select * from test1 group by x1, x2;
>
>ERROR: illegal use of aggregate or non-group column in target list

OK, in your original posting you had: select x1, x2, x3, x4 on tablex order
by x1, x2.

...which has little to do with the statement you are now posting; (the
former has a syntax error and uses ORDER BY, the second has an SQL error
and uses GROUP BY).

>I believe, that all fields are in group column, but what means target list:
>is it the native table test1 or is mean  the output list, which is seen on
>screen
>after the statement?

The target list is the list of fields that you SELECT statement will
return, in your select * statement, these fields are: x1,x2 and tx.

>And another part is: the same table and the same statement bring out the
>correct values in a postgreql v6.4.x . Why not in v6.5?

I've tried your SELECTs under postgres 6.4.0 and I'm begining to see where
the confusion might have arose.

PG6.5 is perfectly correct to flag up an error with the statement "select *
from test1 group by x1, x2;"

Think of it this way: you are asking it to form distinct groups on the
basis of having a unique combination of x1 and x2; then you ask, for each
such group, for the values of x1, x2 and tx.  The problem is, for the group
where the value of x1 is 1 and the value of x2 is 2, there are two valid
values of tx ('a' and 'b').

Postgres can't -and indeed shouldn't have to- resolve this ambiguity so it
flags an error in version 6.5.  Unfortunately it does not in version 6.4
which I would consider a bug!

You get the appropriate error message under PG 6.4 if you try the following:

SELECT *,count(tx) FROM test1 GROUP BY x1, x2;

To get your statement to work under PG6.5 you must either include the tx
field into your GROUP BY list:

SELECT * FROM test1 GROUP BY x1, x2,tx;

or drop it from your target list:

SELECT x1, x2 FROM test1 GROUP BY x1, x2,tx;

Personally, I think that you need to rethink exactly what you are trying to
do with your select and chose the appropriate solution.

The good news is that PG6.5 probably saved you from many silent errors
(because your select statement under PG6.4 could not be guaranteed to
always return the same value for tx!).

Hope this helps.

Regards,

Stuart.

+--------------------------+--------------------------------------+
| Stuart C. G. Rison       | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street               |
| N.B. new phone code!!    | London, W1P 8BT                      |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM                       |
| Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk              |
+--------------------------+--------------------------------------+